package com.cndw.kongfu;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FilenameFilter;
import java.io.IOException;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;

public class InsertDB {

	private static final String dir = "E://excel2db/0704/test";

	public static void main(String[] args) {
		try {
			// 创建对Excel工作簿文件的引用­
			File fileDir = new File(dir);
			if (!fileDir.isDirectory()) {
				System.out.println("dir must to be a directory");
				return;
			}
			File[] files = fileDir.listFiles(new FilenameFilter() {

				@Override
				public boolean accept(File dir, String name) {
					if (name.endsWith(".xls")) {
						return true;
					}
					return false;
				}
			});
			Workbook wookbook = null;
			InputStream is = null;
			String table = "";
			for (File f : files) {
				is = new FileInputStream(f);
				wookbook = new HSSFWorkbook(is);
				
				int sheets = wookbook.getNumberOfSheets();
				for (int sn = 0; sn < sheets; sn++) {
					
					HSSFSheet sheet = (HSSFSheet) wookbook.getSheetAt(sn);
					table = sheet.getSheetName();
					String columns = getColumns(sheet);
					// 获取到Excel文件中的所有行数
					int rows = sheet.getPhysicalNumberOfRows();
					// 遍历行
					for (int i = 1; i < rows; i++) {
						HSSFRow row = sheet.getRow(i);
						StringBuffer sql = 	new StringBuffer("INSERT INTO `" + table + "` ( " + columns + ") VALUES(");
						// 行不为空
						if (row != null) {
							// 获取到Excel文件中的所有的列­
							int cells = row.getPhysicalNumberOfCells();
							
							String value = "";
							// 遍历列
							
							for (int j = 0; j < cells; j++) {
								// 获取到列的值
								HSSFCell cell = row.getCell(j);
								
								if (cell != null) {
									switch (cell.getCellType()) {
									case HSSFCell.CELL_TYPE_FORMULA:
										break;
									case HSSFCell.CELL_TYPE_NUMERIC:
										sql.append((int)cell.getNumericCellValue()).append(",");
										
										break;
									case HSSFCell.CELL_TYPE_STRING:
										sql.append("'").append(cell.getStringCellValue()).append("',");
										break;
									case HSSFCell.CELL_TYPE_BLANK:
										sql.append("'',");
										break;
									default:
										
										break;
									}
								}
							}
							System.out.println(sql.substring(0, sql.length() - 1) + ")");
							
						}
					}
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}
	
	public static String getColumns(HSSFSheet sheet) {
		StringBuffer columns = new StringBuffer();
		HSSFRow columnRow = sheet.getRow(0);
		int cells = columnRow.getPhysicalNumberOfCells();
		for (int i = 0; i < cells; i++) {
			// 获取到列的值
			HSSFCell cell = columnRow.getCell(i);
			
			if (cell != null) {
				switch (cell.getCellType()) {
				case HSSFCell.CELL_TYPE_FORMULA:
					break;
				case HSSFCell.CELL_TYPE_NUMERIC:
					columns.append((int)cell.getNumericCellValue()).append(",");
					break;
				case HSSFCell.CELL_TYPE_STRING:
					columns.append("`").append(cell.getStringCellValue()).append("`,");
					break;
				case HSSFCell.CELL_TYPE_BLANK:
					
					break;
				default:
					
					break;
				}
			}
		}
		return columns.substring(0, columns.length() - 1);
	}
}

